package java6703.lesson11;

import java.sql.*;
import java.util.Scanner;

public class Select6703 {

    private static final String DRIVER;
    private static final String URL;
    private static final String USER;
    private static final String PW;

    static {
        //使用SQLite
        DRIVER = "org.sqlite.JDBC";
        URL = "jdbc:sqlite:\\C:\\Users\\admin、\\IdeaProjects\\java6703\\11Lesson6703\\db6703.db3";
        USER = "";
        PW = "";
    }

    public static void main(String[] args) {
        Scanner sc = new Scanner(System.in);
        System.out.printf("请输入要查找的名字中包含的任何信息：");
        String strFind = sc.nextLine();
        System.out.printf("请输入id范围：");
        int maxId = sc.nextInt();
        find6703(strFind, maxId);
    }


    /*
    （1）使用PreparedStatement执行SELECT语句；
    （2）使用静态代码块设置连接参数；
    （3）完整的异常处理：使用ARM处理各种可能的异常；
    （4）使用静态代码块使用静态代码块初始化数据库连接参数；
     */
    private static void find6703(String strFind, int maxId) {
        Connection conn = null;
        PreparedStatement preS = null;
        try {
            Class.forName(DRIVER);
            conn = DriverManager.getConnection(URL, USER, PW);
            String sql = "SELECT ID,SNO,SNAME FROM xslist WHERE SNAME LIKE ? AND ID < ?";

            //使用PreparedStatement执行SELECT语句
            preS = conn.prepareStatement(sql);

            //按照输入要求查找
            preS.setString(1, "%" + strFind + "%");
            preS.setInt(2, maxId);
            ResultSet rs = preS.executeQuery();
            String sno, sname;

            while (rs.next()) {
                sno = rs.getString("sno");
                sname = rs.getString("sname");
                System.out.printf("%-4d,%9s,%-10s\n", rs.getInt(1), sno, sname);
            }
            rs.close();
            preS.close();
            conn.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            if (preS != null) {
                try {
                    preS.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}